/**
 * 
 */
package com.dfhc.dbbatch;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import com.dfhc.util.StringHelper;

/**
 * @author 龙色波
 * 批量SQL处理
 */
public class BatchSQL {

	/**
	 * @param args
	 * @throws IOException 
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
		
		String sqlFileName="D:\\临时\\testseq.sql";
		
		
		
		
		Connection conn =null;
		Statement cmd = null;
		BufferedReader bufferreader = null;
		long count = 0;
		try{
		conn= getConnection();
		
		cmd = conn.createStatement();
		long start = System.currentTimeMillis();	
		bufferreader = new BufferedReader(new FileReader(sqlFileName));
		
		long start1 = System.currentTimeMillis();
		while(true){
			
			
			String sql = bufferreader.readLine();
			if(!StringHelper.isEmpty(sql)){
				sql = StringHelper.replaceAll(sql, ";", "").toString();
			
				cmd.addBatch(sql);
				
				count = count+1;
				if(count %1000 ==0){
					cmd.executeBatch();
					conn.commit();
					
					System.out.println("批量插入1000条花:"+(System.currentTimeMillis()-start1));
					start1 = System.currentTimeMillis();
				}
			}else{				
				break;
			}
			
		}
		if(count %1000 !=0){
			cmd.executeBatch();
			conn.commit();
		}
		long end = System.currentTimeMillis();
		System.out.println("批量插入花:"+(end-start));
		}finally{
			if(bufferreader!=null){
				bufferreader.close();
			}
			if(cmd!=null){
				cmd.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
	}
	/**
	 * 获取数据库连接
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	private static Connection getConnection() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		String url="jdbc:mysql://127.0.0.1:3306/miit?userServerPrepStmts=false&rewriteBatchedStatements=true";
		Connection conn = DriverManager.getConnection(url,"root",null);
		conn.setAutoCommit(false);
		return conn;
	}

}
